//201902104003邵潇漩
package bysj.dao;

import bysj.domain.*;
import bysj.service.*;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public final class GraduateProjectDao {
    private static GraduateProjectDao graduateProjectDao = null;

    private GraduateProjectDao() {
    }

    public static GraduateProjectDao getInstance() {
        if (graduateProjectDao == null){
            graduateProjectDao = new GraduateProjectDao();
        }
        return graduateProjectDao;
    }

   public static Collection<GraduateProject> findAll(Connection connection) throws SQLException {
       Collection<GraduateProject> graduateProjects = new TreeSet<GraduateProject>();
       String sql_select = "select * from graduateProject";
       //获得查询的预编译语句对象
       PreparedStatement pstmt = connection.prepareStatement(sql_select);
       //执行sql查询语句并获得结果集对象
       ResultSet resultSet = pstmt.executeQuery();
       //遍历结果集，根据元组创建对象，并添加到集合中
       while(resultSet.next()){
           graduateProjects.add(new GraduateProject(resultSet.getInt("id"),
                   resultSet.getString("title"),
                   resultSet.getDouble("score"),
                   GraduateProjectCategoryService.getInstance().find(resultSet.getInt("graduateProjectCategory_id")),
                   GraduateProjectTypeService.getInstance().find(resultSet.getInt("graduateProjectType_id")),
                   GraduateProjectStatusService.getInstance().find(resultSet.getInt("graduateProjectStatus_id")),
                   GraduateProjectSourceService.getInstance().find(resultSet.getInt("graduateProjectSource_id")),
                   GraduateProjectTruenessService.getInstance().find(resultSet.getInt("graduateProjectTrueness_id")),
                   TeacherService.getInstance().find(resultSet.getInt("teacher_id")),
                   StudentService.getInstance().find(resultSet.getInt("student_id")),
                   resultSet.getInt("year")
           ));
       }
       JdbcHelper.close(resultSet,pstmt);
       return graduateProjects;
   }


    public static Collection<GraduateProject> findAll(Pagination pagination, List<Condition> conditionList, Connection connection) throws SQLException {
        Collection<GraduateProject> graduateProjects = new TreeSet<GraduateProject>();
        int totalNum = CommonDao.getInstance().count(connection,"GraduateProject");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM GraduateProject");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }

        //获得查询的预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        //执行sql查询语句并获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        //遍历结果集，根据元组创建对象，并添加到集合中
        while(resultSet.next()){
            graduateProjects.add(new GraduateProject(resultSet.getInt("id"),
                    resultSet.getString("title"),
                    resultSet.getDouble("score"),
                    GraduateProjectCategoryService.getInstance().find(resultSet.getInt("graduateProjectCategory_id")),
                    GraduateProjectTypeService.getInstance().find(resultSet.getInt("graduateProjectType_id")),
                    GraduateProjectStatusService.getInstance().find(resultSet.getInt("graduateProjectStatus_id")),
                    GraduateProjectSourceService.getInstance().find(resultSet.getInt("graduateProjectSource_id")),
                    GraduateProjectTruenessService.getInstance().find(resultSet.getInt("graduateProjectTrueness_id")),
                    TeacherService.getInstance().find(resultSet.getInt("teacher_id")),
                    StudentService.getInstance().find(resultSet.getInt("student_id")),
                    resultSet.getInt("year")
            ));
        }
        JdbcHelper.close(resultSet,pstmt);
        return graduateProjects;
    }

    public GraduateProject find(Integer id,Connection conn) throws SQLException {
        GraduateProject desiredGraduateProject = null;
        //创建查询的主语句
        String sql_select_id="SELECT*FROM GraduateProject WHERE id=?";
        //获得查询的预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(sql_select_id);
        pstmt.setInt(1,id);
        ResultSet resultSet=pstmt.executeQuery();
        if(resultSet.next()){
            String title=resultSet.getString("title");
            Double score = resultSet.getDouble("score");
            GraduateProjectCategory graduateProjectCategory=
                    GraduateProjectCategoryService.getInstance().find(resultSet.getInt("graduateProjectCategory_id"));
            GraduateProjectType graduateProjectType=
                    GraduateProjectTypeService.getInstance().find(resultSet.getInt("graduateProjectType_id"));
            GraduateProjectStatus graduateProjectStatus=
                    GraduateProjectStatusService.getInstance().find(resultSet.getInt("graduateProjectStatus_id"));
            GraduateProjectSource graduateProjectSource=
                    GraduateProjectSourceService.getInstance().find(resultSet.getInt("graduateProjectSource_id"));
            GraduateProjectTrueness graduateProjectTrueness=
                    GraduateProjectTruenessService.getInstance().find(resultSet.getInt("graduateProjectTrueness_id"));
            Teacher teacher=TeacherService.getInstance().find(resultSet.getInt("teacher_id"));
            Student student=StudentService.getInstance().find(resultSet.getInt("student_id"));
            int year=resultSet.getInt("year");
            desiredGraduateProject=new GraduateProject
                    (id,title,score,graduateProjectCategory,graduateProjectType,
                            graduateProjectStatus,graduateProjectSource,graduateProjectTrueness,teacher,student,year);
        }
        JdbcHelper.close(resultSet,pstmt);
        return desiredGraduateProject;
    }

    public boolean update(GraduateProject graduateProject,Connection conn) throws SQLException {
        //创建sql语句
        String updateGraduateProject_sql = "UPDATE GraduateProject SET title=?, score=?, graduateProjectCategory_id=?, " +
                "graduateProjectType_id=? ,graduateProjectStatus_id=?,graduateProjectSource_id=?,graduateProjectTrueness_id=?," +
                "teacher_id=?,student_id=?,year=? WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(updateGraduateProject_sql);
        // 为预编译参数赋值
        pstmt.setString(1,graduateProject.getTitle());
        pstmt.setDouble(2,graduateProject.getScore());
        pstmt.setInt(3,graduateProject.getGraduateProjectCategory().getId());
        pstmt.setInt(4,graduateProject.getGraduateProjectType().getId());
        pstmt.setInt(5,graduateProject.getGraduateProjectStatus().getId());
        pstmt.setInt(6,graduateProject.getGraduateProjectSource().getId());
        pstmt.setInt(7,graduateProject.getGraduateProjectTrueness().getId());
        pstmt.setInt(8,graduateProject.getTeacher().getId());
        pstmt.setInt(9,graduateProject.getStudent().getId());
        pstmt.setInt(10,graduateProject.getYear());
        pstmt.setInt(11,graduateProject.getId());
        //执行预编译语句的executeUpdate方法,获得修改的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(pstmt);
        //返回GraduateProject对象是否修改成功
        return affectedRowNum>0;
    }

    public boolean add(GraduateProject graduateProject,Connection conn) throws SQLException {
        String addGraduateProject_sql="INSERT INTO GraduateProject "+"(title,score,graduateProjectCategory_id," +
                "graduateProjectType_id,graduateProjectStatus_id,graduateProjectSource_id," +
                "graduateProjectTrueness_id,teacher_id,student_id,year)value(?,?,?,?,?,?,?,?,?,?)";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(addGraduateProject_sql);
        //为预编译参数赋值
        pstmt.setString(1, graduateProject.getTitle());
        pstmt.setDouble(2, graduateProject.getScore());
        pstmt.setInt(3, graduateProject.getGraduateProjectCategory().getId());
        pstmt.setInt(4,graduateProject.getGraduateProjectType().getId());
        pstmt.setInt(5,graduateProject.getGraduateProjectStatus().getId());
        pstmt.setInt(6,graduateProject.getGraduateProjectSource().getId());
        pstmt.setInt(7,graduateProject.getGraduateProjectTrueness().getId());
        pstmt.setInt(8,graduateProject.getTeacher().getId());
        pstmt.setInt(9,graduateProject.getStudent().getId());
        pstmt.setInt(10,graduateProject.getYear());
        //执行预编译语句的executeUpdate方法,获得修改的记录行数
        int rowAffected =pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rowAffected>0;
    }

    public boolean delete(Integer id,Connection conn) throws SQLException {
        //创建sql语句
        String deleteGraduateProject_sql = "DELETE FROM GraduateProject WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(deleteGraduateProject_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译语句的executeUpdate方法,获得删除的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(pstmt);
        //如果删除记录的行数不为0，返回true,否则返回false
        return affectedRowNum>0;
    }
}
